CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetReportActivityDetails`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE parentId INT;
    DECLARE parentName VARCHAR(255);

    -- Declare cursor to select parent activities
    DECLARE cur CURSOR FOR 
    SELECT ad.Id, ad.Name
    FROM activity_details ad
    WHERE ad.`Type` = 2;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Temporary table to store final results
    DROP TEMPORARY TABLE IF EXISTS finalResult;
    CREATE TEMPORARY TABLE finalResult (
        CombinedData JSON
    );

    -- Open cursor to fetch parent activities
    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO parentId, parentName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Insert parent and its associated child activities into finalResult
        INSERT INTO finalResult (CombinedData)
        SELECT 
            JSON_OBJECT(
                'ParentId', parentId,
                'ParentName', parentName,
                'ActivtyType', JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'Id', ad1.Id,
                        'Name', ad1.Name,
                        'Type', ad1.`Type`,
                        'UniqueId', ad1.UniqueId
                    )
                )
            ) AS CombinedData
        FROM activity_details ad1
        WHERE ad1.MappedType = parentId;

    END LOOP;

    CLOSE cur;

    -- Select the final results
    SELECT * FROM finalResult;

    /*DROP TEMPORARY TABLE IF EXISTS finalResult;*/
END